﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Reflection;
using System.Data;
using System.IO;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using DAL;
using Common;

namespace 包覆门.OrderManager
{
    public class ExportWlExcel
    {
        #region 变量
        //public static String connetStr = "server=192.168.11.91;port=3306;user=bfm;password=123456; database=baofumen;Allow User Variables=True";
        //MySqlConnection ms = new MySqlConnection(connetStr);

        public string Danhao { get; set; }//单号
        private OrderDAL dal = new OrderDAL();//数据仓库
        public bool IsDoton { get; set; }//是否是大唐订单
        readonly object miss = Missing.Value;
        readonly Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.Application();//excel实例
        //excel 模板
        //readonly string modelPath = AppDomain.CurrentDomain.BaseDirectory + "\\导出物料清单.xls";
        //readonly string textPath = AppDomain.CurrentDomain.BaseDirectory + "\\导出物料清单.txt";

        readonly string modelPath = AppDomain.CurrentDomain.BaseDirectory + CommonHelper.GetPath("orderwlpath");
        readonly string textPath = AppDomain.CurrentDomain.BaseDirectory + CommonHelper.GetPath("orderwlDescpath");
       // readonly string exportPath = AppDomain.CurrentDomain.BaseDirectory + "\\exportExcel\\导出物料清单"+DateTime.Now.ToString("yyyyMMddHHmmss")+".xls";
        //数据源
        DataTable mainorder = new DataTable();
        DataTable detailorder = new DataTable();
        DataTable wlorder = new DataTable();
        int ii = 0;

        #endregion 变量


        #region 方法
        public void OutExcel()
        {
            mainorder = dal.ReportMainOrderByDanhao(Danhao);//获取数据源
            mainorder.Columns.Add(new DataColumn("xdrq",typeof(string)));
            mainorder.Columns.Add(new DataColumn("xbrq",typeof(string)));
            mainorder.Columns.Add(new DataColumn("jhrq",typeof(string)));
            mainorder.Rows[0]["xdrq"] = DateTime.Now.ToString("yyyy-MM-dd");
            mainorder.Rows[0]["xbrq"] = DateTime.Now.AddDays(7).ToString("yyyy-MM-dd");
            mainorder.Rows[0]["jhrq"] = DateTime.Now.AddDays(17).ToString("yyyy-MM-dd");

            DataTable detailIntial = dal.ReportWLDetailOrderByDanhao(Danhao);//获取详情表
            detailorder  = detailIntial.Clone();

            //导出路径
            string name =IsDoton?"大唐订单物料":"OEM订单物料";
            string exportPath = AppDomain.CurrentDomain.BaseDirectory + "\\exportExcel\\" + name + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";

            foreach (DataRow dr in detailIntial.Rows)
            {
                if (dr["zslb"].ToString().Equals("0"))
                {
                    if (!dr["iszh"].ToString().Equals("0"))
                    {
                        AddDataRowToTable(dr, true);
                        ii = ii + 2;
                    }
                    else
                    {
                        AddDataRowToTable(dr, false);
                        ii = ii + 1;
                    }
                }
                else
                {
                    AddDataRowToTable(dr, false);
                    ii = ii + 1;
                }
            }
            wlorder = dal.ReportWlsl(Danhao);//物料单
            int index1 = 0;
            int index2 = 0;
            string strText = "";
            var readText = new StreamReader(textPath, Encoding.GetEncoding("GB2312"));
            var mode = new FileInfo(modelPath);
            try
            {
                mode.CopyTo(exportPath);
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
                myExcel.Workbooks.Close();
                myExcel.Quit();
            }
            myExcel.Visible = true;
            myExcel.Application.Workbooks.Open(exportPath, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
            var mySheet = (Microsoft.Office.Interop.Excel.Worksheet)myExcel.Sheets[1];
            //进度条
           // WaitingForm wf = new WaitingForm();
            
           // wf.StartWaiting();
            while (strText != null)
            {
                if (!strText.StartsWith("--") && strText != "")
                {
                    //string[] sArray = Regex.Split(strTxt, "::", RegexOptions.IgnoreCase);
                    //string[] XYAxis = sArray[3].Split(',');
                    string[] sArray = Regex.Split(strText, "::", RegexOptions.IgnoreCase);
                    string[] xyAxis = sArray[3].Split(',');
                    int xAxis = Convert.ToInt16(xyAxis[0]);
                    int yAxis = Convert.ToInt16(xyAxis[1]);
                    //int detail = 0;
                    //int wl = 0;
                    try
                    {
                        switch (sArray[1])
                        {
                            case "MAINORDER":
                                if (mainorder.Rows.Count <= 0)
                                    break;
                                mySheet.Cells[xAxis, yAxis] = mainorder.Rows[0][sArray[2]];
                                break;
                            case "DETAILORDER":
                                if (detailorder.Rows.Count <= 0)
                                    break;
                                for (int i = 0; i < detailorder.Rows.Count; i++)
                                {
                                    if (xAxis + i > 10 && yAxis == 1)
                                    {
                                        InsertRow(11, index1, mySheet);
                                        index1 = index1 + 1;
                                    }
                                    mySheet.Cells[xAxis + i, yAxis] = detailorder.Rows[i][sArray[2]];
                                }
                                break;
                            case "DETAILORDERSTATIS":
                                mySheet.Cells[xAxis + index1, yAxis] = detailorder.Compute("sum(" + sArray[2] + ")", "isdel='0'");
                                break;
                            case "WLORDER":
                                if (wlorder.Rows.Count <= 0)
                                    break;
                                for (int i = 0; i < wlorder.Rows.Count; i++)
                                {
                                    if (xAxis + i > 13 && yAxis == 1)
                                    {
                                        InsertRow1(14, index1 + index2, mySheet);
                                        index2 = index2 + 1;
                                    }
                                    mySheet.Cells[xAxis + i + index1, yAxis] = wlorder.Rows[i][sArray[2]];
                                }
                                break;
                            case "WLTJ":
                                mySheet.Cells[xAxis + index1 + index2, yAxis] = wlorder.Compute("sum(" + sArray[2] + ")", "");
                                break;
                        }
                    }
                    catch (Exception ex)
                    {

                        MessageBox.Show(@"数据读取错误!" + ex.Message);
                    }
                }

                strText = readText.ReadLine();
            }
          //  wf.StopWaiting();
            //myExcel.Visible = true;

        }
        #region 删除
        //public DataTable GetMainorder()
        //{
            //DataTable dt = new DataTable();
            //dt.Columns.Add(new DataColumn("username", typeof(System.String)));
            //dt.Columns.Add(new DataColumn("sname", typeof(System.String)));
            //dt.Columns.Add(new DataColumn("danhao", typeof(System.String)));
            //dt.Columns.Add(new DataColumn("xdrq", typeof(System.String)));
            //dt.Columns.Add(new DataColumn("xbrq", typeof(System.String)));
            //dt.Columns.Add(new DataColumn("jhrq", typeof(System.String)));
            //dt.Columns.Add(new DataColumn("planorder", typeof(System.String)));
            //DataRow dr = dt.NewRow();
            //dr["username"] = "张三";
            //dr["sname"] = "王二";
            //dr["danhao"] = "TA0029323";
            //dr["xdrq"] = "2018-05-12";
            //dr["xbrq"] = "2018-05-16";
            //dr["jhrq"] = "2018-05-22";
            //dr["planorder"] = "TAB0029323";
            //dt.Rows.Add(dr);
            //return dal.ReportMainOrderByDanhao(Danhao); ;
        //}
        //获取详情表
        //public DataTable GetDetailOrder()
        //{
        //    ms.Open();
        //    String str = "select (@i:=@i+1) as xh , D.pjfs,D.kxbh,D.xbmc,D.gao,D.hou,D.kuan,D.color,D.num,D.direction,D.bzbz,D.oldcolor,D.xbwidth,D.xbheight,D.zslb,D.iszh from detailorder D ,(select @i:=0) as B where danhao='TADYQ9920323'";
        //    MySqlDataAdapter ad = new MySqlDataAdapter(str, ms);
        //    DataSet dsdx = new DataSet();
        //    ad.Fill(dsdx);
        //    return detailorder; 
        //}

        //public DataTable GetWlOrder()
        //{
        //    DataTable dt = new DataTable();
        //    dt.Columns.Add(new DataColumn("xh", typeof(System.String)));
        //    dt.Columns.Add(new DataColumn("wlmc", typeof(System.String)));
        //    dt.Columns.Add(new DataColumn("color", typeof(System.String)));
        //    dt.Columns.Add(new DataColumn("unit", typeof(System.String)));
        //    dt.Columns.Add(new DataColumn("num", typeof(System.Int32)));
        //    DataRow dr = null;
        //    for (int i = 0; i < 4; i++)
        //    {
        //        dr = dt.NewRow();
        //        dr["xh"] = i + 1;
        //        dr["wlmc"] = "物料" + i;
        //        dr["color"] = "颜色" + i;
        //        dr["unit"] = "支";
        //        dr["num"] = i + 10;
        //        dt.Rows.Add(dr);
        //    }
        //    return dt;
        //}
        #endregion 删除

        public void AddDataRowToTable(DataRow dr, bool flag)
        {
            if (flag)
            {
                string top = dr["xbTopheight"].ToString();
                string down = dr["xbDownheight"].ToString();
                string topName = dr["xbmc"] + "(上芯板)";
                string downName = dr["xbmc"] + "(下芯板)";
                DataRow drNew = detailorder.NewRow();
                drNew = dr;
                drNew["xbheight"] = top;
                drNew["xbmc"] = topName;
                drNew["xh"] = ii;
                detailorder.ImportRow(drNew);
                DataRow drNew1 = detailorder.NewRow();
                //drNew1 = dr;
                drNew1["xh"] = (ii + 1);
                drNew1["xbwidth"] = dr["xbwidth"];
                drNew1["xbheight"] = down;
                drNew1["xbmc"] = downName;
                detailorder.Rows.Add(drNew1);
            }
            else
            {
                dr["xh"] = ii;
                detailorder.ImportRow(dr);
            }
        }
        //获取物料表单




        /// <summary>
        /// 增加门型行
        /// </summary>
        /// <param name="BeginRowNumber"> 起始号码</param>
        /// <param name="InD">增加的行数</param>
        /// <param name="workSheet">工作表</param>
        public void InsertRow(int BeginRowNumber, int InD, Microsoft.Office.Interop.Excel.Worksheet workSheet)
        {
            var range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[BeginRowNumber + InD, Type.Missing];
            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 1], workSheet.Cells[BeginRowNumber + InD, 13]];
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            //range = workSheet.get_Range(workSheet.Cells[BeginRowNumber + InD, 3], workSheet.Cells[BeginRowNumber + InD, 44]);
            //range.Merge(Missing.Value);
            //range.Select();
            //RunMacro("BK");
        }
        /// <summary>
        /// 增加物料名称
        /// </summary>
        /// <param name="BeginRowNumber"> 起始号码</param>
        /// <param name="InD">增加的行数</param>
        /// <param name="workSheet">工作表</param>
        public void InsertRow1(int BeginRowNumber, int InD, Microsoft.Office.Interop.Excel.Worksheet workSheet)
        {
            var range = (Microsoft.Office.Interop.Excel.Range)workSheet.Rows[BeginRowNumber + InD, Type.Missing];
            range.EntireRow.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, Microsoft.Office.Interop.Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 2], workSheet.Cells[BeginRowNumber + InD, 3]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 4], workSheet.Cells[BeginRowNumber + InD, 5]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 6], workSheet.Cells[BeginRowNumber + InD, 7]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 8], workSheet.Cells[BeginRowNumber + InD, 9]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 10], workSheet.Cells[BeginRowNumber + InD, 11]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            range = workSheet.Range[workSheet.Cells[BeginRowNumber + InD, 12], workSheet.Cells[BeginRowNumber + InD, 13]];
            range.Merge(Missing.Value);
            range.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            //range = workSheet.get_Range(workSheet.Cells[BeginRowNumber + InD, 3], workSheet.Cells[BeginRowNumber + InD, 44]);
            //range.Merge(Missing.Value);
            //range.Select();
            // RunMacro("GSS");

        }



        /// <summary>
        /// 运行Excel宏
        /// </summary>
        /// <param name="MacroName"></param>
        public void RunMacro(string MacroName)
        {
            myExcel.Run(MacroName, miss, miss,

                miss, miss, miss, miss, miss, miss, miss,

                miss, miss, miss, miss, miss, miss, miss,

                miss, miss, miss, miss, miss, miss, miss,

                miss, miss, miss, miss, miss, miss, miss);
        }

        #endregion
    }
}
